import asyncio
import traceback
import oss2
import os
import uuid
import json
import aiohttp
import datetime
import re
import hashlib

from urllib.parse import quote
from aiohttp import ClientTimeout
from itertools import groupby
from common.log import logger
from db.mysql.entity_service import EntitiesDao
from db.redis.redis_service import RedisMemory

from bot.insurance_planner_gpt.qw_server.qw_process import qw_process_msg

entities_dao = EntitiesDao()

redis_memory = RedisMemory()
host = 'https://api.suolink.cn/api.htm?url='
appcode = '6457bcae2ea82d898fc82c0dcb3@6a826df1261078fac191882680e93b19'

MSG_TYPE_JSON = {'text': 2,
                 'image': 3,
                 'link': 4,
                 'video': 5,
                 'voice': 6,
                 'mini_program': 7,
                 'name_card': 8,
                 'gif': 9,
                 'video_channel': 11,
                 'red_packet': 12,
                 'file': 13
                 }
endpoint = "https://oss-cn-beijing.aliyuncs.com"
accessKeyId = "LTAI5tD6wjouyagutDYhF226"
accessKeySecret = "KdPZ4YMvu8tsjXusqHBYmFGJK1ThQw"
bucketName = "wxwork-chat"
record_path = os.path.dirname(os.path.realpath(__file__))
auth = oss2.Auth(accessKeyId, accessKeySecret)
bucket = oss2.Bucket(auth, endpoint, bucketName)
if not os.path.exists(os.path.join(record_path, "sop_img")):
    os.mkdir(os.path.join(record_path, "sop_img"))


async def send_wechat_sop_message(add_wechat_day, msg, msg_type):
    msg_type_int = MSG_TYPE_JSON.get(str(msg_type))
    logger.info('收到请求发送圈选数据 圈选标签:{} 发送对话内容:{} 发送类型:{}'.format(add_wechat_day, msg, msg_type))
    if msg_type_int and add_wechat_day and msg:
        sql = getSql()
        result = entities_dao.select_sop_list(sql)
        result_list = list(result)
        result_list.sort(key=lambda x: x[8])
        request_time = datetime.datetime.now().strftime("%Y-%m-%d")
        for key, group in groupby(result_list, lambda x: x[8]):
            group_list = list(group)
            if key == add_wechat_day and group_list:
                asyncio.ensure_future(send_msg(key, group_list, msg, request_time, msg_type_int))
                return {'msg': 'OK', 'code': '200'}
            else:
                return {'msg': '没有获取到该标签 或者查询的数据为空', 'code': '500'}

    else:
        return {'msg': '请核实发送类型 圈选标签 发送内容', 'code': '500'}


def getSql():
    return '''with 加微成功记录 as (select contact_id
                                   , robot_id
                                   , add_time
                                   , nick_name
                                   , concat(robot_id, ':', contact_id) as robot_contact
                                   , datediff(current_date, add_time)  as 添加天数
                              from ql_add_friend_result
                              where company_id = 2242
                                and status = 1)
           , 微信聊天记录 as (select session_id
                                   , role
                                   , message_id
                                   , attitude
                                   , msg_type
                                   , text_info
                                   , message_time
                                   , client_type
                              from ql_qw_contact_msg
                              where session_id in (select robot_contact from 加微成功记录)
                                and text_info != '我通过了你的联系人验证请求，现在我们可以开始聊天了')
           , 最后一次客户回复 as (select last_user.session_id
                                       , last_user.message_time
                                       , datediff(current_date, last_user.message_time) 用户最后回复间隔
                                  from (select 微信聊天记录.session_id
                                             , 微信聊天记录.role
                                             , 微信聊天记录.message_id
                                             , 微信聊天记录.attitude
                                             , 微信聊天记录.msg_type
                                             , 微信聊天记录.text_info
                                             , 微信聊天记录.message_time
                                             , 微信聊天记录.client_type
                                             , row_number() over (partition by 微信聊天记录.session_id order by 微信聊天记录.message_time desc) as row_num
                                        from 微信聊天记录
                                        where role = 'user') as last_user
                                  where last_user.row_num = 1)
           , 最后一次机器人回复 as (select last_robot.session_id
                                         , last_robot.message_time
                                         , datediff(current_date, last_robot.message_time) 机器人最后回复间隔
                                    from (select 微信聊天记录.session_id
                                               , 微信聊天记录.role
                                               , 微信聊天记录.message_id
                                               , 微信聊天记录.attitude
                                               , 微信聊天记录.msg_type
                                               , 微信聊天记录.text_info
                                               , 微信聊天记录.message_time
                                               , 微信聊天记录.client_type
                                               , row_number() over (partition by 微信聊天记录.session_id order by 微信聊天记录.message_time desc) as row_num
                                          from 微信聊天记录
                                          where role = 'assistant') as last_robot
                                    where last_robot.row_num = 1)

           , 统计数据 as (select 加微成功记录.robot_contact
                               , 加微成功记录.contact_id
                               , 加微成功记录.robot_id
                               , 加微成功记录.添加天数                             as add_date_day
                               , case
                                     when 加微成功记录.添加天数 = 0 then 0
                                     when 加微成功记录.添加天数 = 1 then 1
                                     when 加微成功记录.添加天数 = 3 then 3
                                     when 加微成功记录.添加天数 = 7 then 7
                                     when 加微成功记录.添加天数 = 14 then 14
                                     when 加微成功记录.添加天数 = 25 then 25
                                     else 加微成功记录.添加天数 end                                   as user_add_date
                               , 加微成功记录.nick_name
                               , 加微成功记录.add_time
                               , ifnull(最后一次客户回复.用户最后回复间隔, -1)     as last_user
                               , ifnull(最后一次机器人回复.机器人最后回复间隔, -1) as last_robot
                          from 加微成功记录
                                   left join 最后一次客户回复 on 加微成功记录.robot_contact = 最后一次客户回复.session_id
                                   left join 最后一次机器人回复 on 加微成功记录.robot_contact = 最后一次机器人回复.session_id)

        select robot_contact,
               contact_id,
               robot_id,
               add_date_day,
               user_add_date,
               nick_name,
               add_time,
               last_user,
               case
                   when user_add_date = 0 then 'D0'
                   when user_add_date = 1 then 'D1'
                   when user_add_date = 3 and last_user between 1 and 2 then 'D3-Y'
                   when user_add_date = 3 then 'D3-N'
                   when user_add_date = 7 and last_user between 1 and 4 then 'D7-Y'
                   when user_add_date = 7 then 'D7-N'
                   when user_add_date = 14 and last_user between 1 and 7 then 'D14-Y'
                   when user_add_date = 14 then 'D14-N'
                   when user_add_date = 25 and last_user between 1 and 11 then 'D25-Y'
                   when user_add_date = 25 then 'D25-N'
                   else concat('D',user_add_date) end as user_ask
                ,
               last_robot
        from 统计数据;'''


async def send_msg(day, group_list, msg, request_time, msg_type):
    logger.info('发送消息日志 发送类型:{} 发送者:{} 发送内容:{}'.format(day, group_list, msg))
    expire_date = (datetime.datetime.now() + datetime.timedelta(hours=30)).strftime('%Y-%m-%d %H:%M:%S')
    if msg and group_list:
        for item in group_list:
            uniq_sn = str(uuid.uuid4()).replace("-", "")
            contact_id = item[1]
            robot_id = item[2]
            pattern = r'\*\*\*((?:https?://|www\.)[^\*]+)\*\*\*'
            data_msg = ''
            data_msg = msg
            matches = re.findall(pattern, data_msg)
            url = []
            for match in matches:
                contact_id_md5 = md5_encode(contact_id)
                if '?' in match:
                    new_url = match + '&id={}'.format(contact_id_md5)
                    short_link = await get_short_link(new_url, expire_date)
                else:
                    new_url = match + '?id={}'.format(contact_id_md5)
                    short_link = await get_short_link(new_url, expire_date)
                data = {'url': match, 'short_link': short_link, 'new_url': new_url}
                url.append(data)
                # 使用新链接替换原链接
                data_msg = data_msg.replace('***' + match + '***', str(short_link))
            data = {'day': day, 'request_time': request_time, 'contact_id': contact_id, 'robot_id': robot_id,
                    'uniq_sn': uniq_sn, 'msg': msg, 'url': json.dumps(url), 'expire_date': expire_date,
                    'data_msg': data_msg, 'msg_type': msg_type}
            logger.info('发送消息日志调用 数据:{}'.format(data))
            redis_memory.set_sop_send_msg_list(json.dumps(data))


async def get_sop_send_msg():
    send_msg_data = ''
    while True:
        current_time = datetime.datetime.now().time()
        if datetime.time(9, 0) <= current_time <= datetime.time(12, 0):
            send_msg_status = redis_memory.get_sop_send_msg_status()
            if send_msg_status == '1':
                try:
                    send_msg_data = redis_memory.get_sop_send_msg_list()
                    if send_msg_data and send_msg_data is not None:
                        send_data_json = json.loads(send_msg_data)
                        day = send_data_json.get('day')
                        request_time = send_data_json.get('request_time')
                        contact_id = send_data_json.get('contact_id')
                        robot_id = send_data_json.get('robot_id')
                        uniq_sn = send_data_json.get('uniq_sn')
                        msg = send_data_json.get('msg')
                        url = send_data_json.get('url')
                        expire_date = send_data_json.get('expire_date')
                        data_msg = send_data_json.get('data_msg')
                        msg_type = send_data_json.get('msg_type')

                        if day and request_time and contact_id and robot_id and uniq_sn and msg:
                            send_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
                            response_json = await qw_process_msg.send_msg_to_user('2242', robot_id, contact_id,
                                                                                  data_msg,
                                                                                  msg_type)
                            await asyncio.sleep(5)
                            logger.info(
                                '发送消息日志结果 发送消息:{} 返回消息:{}'.format(send_data_json, response_json))
                            entities_dao.insert_ql_sop_wechat_msg(day, request_time, contact_id, robot_id, uniq_sn,
                                                                  response_json, msg, send_time, url, expire_date,
                                                                  data_msg,
                                                                  msg_type)
                        else:
                            logger.info('[get_sop_send_msg]发送消息 必填字段为空 请检查:{}', send_data_json)
                    else:
                        await asyncio.sleep(5)
                except Exception as e:
                    await asyncio.sleep(5)
                    logger.info('[get_sop_send_msg] 发送消息失败 报错:{} 报错信息:{}', send_msg_data, e)
            elif send_msg_status != '1':
                await asyncio.sleep(5)
                logger.info('[get_sop_send_msg] 没有可发送的数据 睡眠一秒:{}'.format(send_msg_data))
        elif current_time > datetime.time(12, 0):
            await asyncio.sleep(60)
            redis_memory.remove_ql_wechat_msg()
            logger.info('当前时间大于12点 清空未发送的数据 并且睡眠一分钟')
        else:
            await asyncio.sleep(60)
            logger.info('当前时间小于9点 睡眠一分钟')


async def get_short_link(url_data, expireDate):
    short_link = ''
    query_string = quote(url_data)
    url = (host + query_string + '&key=' + appcode + '&expireDate=' + expireDate
           + '&domain=huize.chatlingxi.com&protocol=0')
    error_msg = ''
    try:
        async with aiohttp.ClientSession(timeout=ClientTimeout(total=2)) as session:
            async with session.get(url=url) as response:
                httpStatusCode = response.status
                if httpStatusCode == 200:
                    short_link = await response.text()
                else:
                    error_msg = '生成短链接报错'
    except Exception as e:
        error_msg = '[get_short_link] 获取短连接超时:{} 报错信息:{}'.format(url_data, e)
    finally:
        logger.info('[get_short_link] 短连接:{} 生成的短链接:{}'.format(url_data, short_link))
        if error_msg or url_data == short_link:
            await qw_process_msg.sendMessageToWx('生成短链接报错 或 生成的短链接和原链接一样')
            redis_memory.set_sop_send_msg_status_stop()
        return short_link


async def send_wechat_sop_image(file_name, file_type, file_bytes):
    return_data = {'code': 500}
    logger.info('推送图片到OSS 图片地址:{} '.format(file_name))
    try:
        if file_type.startswith("image") and (
                file_name.endswith(".jpeg") or file_name.endswith(".jpg") or file_name.endswith(".png")
                or file_name.endswith(".gif")):
            uniq = str(uuid.uuid4())
            serverPath = "wechat_sop/" + uniq + ".{}".format(file_name.split(".")[-1])
            logger.info('推送图片路径 本地-【{}】 服务器-【{}】'.format(file_name, serverPath))
            response = bucket.put_object(serverPath, file_bytes)
            if response.status == 200:
                return_data['msg'] = "https://wxwork-chat.oss-cn-beijing.aliyuncs.com/{}".format(serverPath)
                return_data['code'] = response.status
            else:
                logger.info('推送图片失败 【{}】 {}'.format(file_name, response.__dict__))
                return_data['msg'] = '推送图片失败'
        else:
            logger.info('推送文件格式异常 非图片文件 图片地址:{} '.format(file_name))
            return_data['msg'] = '推送文件格式异常'
    except Exception as e:
        logger.info('推送文件异常 【{}】 error:{} '.format(file_name, traceback.format_exc()))
        return_data['msg'] = '推送文件异常'
    finally:
        return return_data


async def get_customer_id_url(customer_id, url):
    expire_date = (datetime.datetime.now() + datetime.timedelta(hours=30)).strftime('%Y-%m-%d %H:%M:%S')
    if customer_id is not None and url is not None and customer_id and url:
        sql = 'select contact_id from ql_work_wechat.ql_add_friend_result where customer_id = {} and company_id = 2242 limit  1'.format(
            customer_id)
        contact_id_list = entities_dao.select_sop_list(sql)
        if contact_id_list is not None and contact_id_list:
            contact_id_list = list(contact_id_list)
            contact_id = contact_id_list[0][0]
            contact_id_md5 = md5_encode(contact_id)
            if '?' in url:
                new_url = url + '&id={}'.format(contact_id_md5)
                short_link = await get_short_link(new_url, expire_date)
            else:
                new_url = url + '?id={}'.format(contact_id_md5)
                short_link = await get_short_link(new_url, expire_date)
            return short_link
        else:
            return '没有找到客户号对应的唯一标识'
    else:
        return '客户号或URL为空'


def md5_encode(data):
    md5 = hashlib.md5()
    md5.update(data.encode('utf-8'))
    return md5.hexdigest()


def send_browsing_history(customer_id, company, url, params_url, request_time):
    pass


async def send_msg_robot(contact_id, robot_id, msg, send_type):
    logger.info('{},{},{},{}'.format(contact_id, robot_id, msg, send_type))
    # logger.info(contact_id, robot_id, msg, send_type)
    return await qw_process_msg.send_msg_to_user('2242', robot_id, contact_id, msg, int(send_type))
